import pg from 'pg';
import redis from 'redis';
import { poolOptimizer } from './connection-pool-optimizer.js';

const { Pool } = pg;

// PostgreSQL连接池配置 - 根据环境优化
const dbPoolConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME || 'bike_wholesale',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'password',
  
  // 连接池大小配置
  max: parseInt(process.env.DB_MAX_CONNECTIONS) || (process.env.NODE_ENV === 'production' ? 20 : 10),
  min: parseInt(process.env.DB_MIN_CONNECTIONS) || (process.env.NODE_ENV === 'production' ? 5 : 2),
  
  // 超时配置 - 增加超时时间避免连接失败
  idleTimeoutMillis: parseInt(process.env.DB_IDLE_TIMEOUT) || 30000,
  connectionTimeoutMillis: parseInt(process.env.DB_CONNECTION_TIMEOUT) || 10000, // 增加到10秒
  acquireTimeoutMillis: parseInt(process.env.DB_ACQUIRE_TIMEOUT) || 30000, // 增加到30秒
  
  // 其他优化参数
  maxUses: parseInt(process.env.DB_MAX_USES) || 7500, // 连接最大使用次数
  allowExitOnIdle: process.env.NODE_ENV !== 'production',
  
  // 连接重试配置
  retry: {
    max: 3,
    timeout: 1000
  },
  
  ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false
};

console.log('🔧 数据库连接池配置:');
console.log(`  环境: ${process.env.NODE_ENV || 'development'}`);
console.log(`  最大连接数: ${dbPoolConfig.max}`);
console.log(`  最小连接数: ${dbPoolConfig.min}`);
console.log(`  连接超时: ${dbPoolConfig.connectionTimeoutMillis}ms`);
console.log(`  获取连接超时: ${dbPoolConfig.acquireTimeoutMillis}ms`);
console.log(`  空闲超时: ${dbPoolConfig.idleTimeoutMillis}ms`);
console.log('');

const dbPool = new Pool(dbPoolConfig);

// Redis客户端配置
const redisClient = redis.createClient({
  url: process.env.REDIS_URL || `redis://${process.env.REDIS_HOST || 'localhost'}:${process.env.REDIS_PORT || 6379}`,
  password: process.env.REDIS_PASSWORD || '',
  database: parseInt(process.env.REDIS_DB) || 0
});

// 增强的数据库连接错误处理
let dbConnectionErrorCount = 0;
const MAX_DB_ERROR_COUNT = 10;
const DB_ERROR_RESET_INTERVAL = 60000; // 1分钟重置错误计数

// 服务状态标志
let isDatabaseAvailable = true;
let isRedisAvailable = true;

dbPool.on('error', (err) => {
  dbConnectionErrorCount++;
  
  console.warn(`⚠️ PostgreSQL连接警告 (${dbConnectionErrorCount}/${MAX_DB_ERROR_COUNT}):`, err.message);
  console.warn(`🔧 连接配置: host=${process.env.DB_HOST || 'localhost'}, port=${process.env.DB_PORT || 5432}, database=${process.env.DB_NAME || 'bike_wholesale'}`);
  
  // 如果错误过多，设置降级标志
  if (dbConnectionErrorCount >= MAX_DB_ERROR_COUNT) {
    isDatabaseAvailable = false;
    console.error('🚨 数据库连接错误过多，已进入降级模式');
    console.error('💡 依赖数据库的功能将被禁用，但API服务仍可访问');
  }
});

// 连接池耗尽保护
dbPool.on('acquire', (client) => {
  console.debug('🔗 获取数据库连接');
});

dbPool.on('release', (err, client) => {
  if (err) {
    console.warn('⚠️ 释放数据库连接时出错:', err.message);
  }
});

// 定期重置错误计数
setInterval(() => {
  if (dbConnectionErrorCount > 0) {
    console.log(`🔄 重置数据库错误计数: ${dbConnectionErrorCount} -> 0`);
    dbConnectionErrorCount = 0;
  }
}, DB_ERROR_RESET_INTERVAL);

redisClient.on('error', (err) => {
  console.warn('Redis连接警告:', err.message);
  console.warn(`🔧 Redis配置: url=${process.env.REDIS_URL || `redis://${process.env.REDIS_HOST || 'localhost'}:${process.env.REDIS_PORT || 6379}`}`);
});

// 连接池监控和资源清理
let connectionPoolStats = {
  totalConnections: 0,
  idleConnections: 0,
  waitingClients: 0,
  lastCheck: new Date()
};

// 监控连接池状态
setInterval(() => {
  connectionPoolStats = {
    totalConnections: dbPool.totalCount,
    idleConnections: dbPool.idleCount,
    waitingClients: dbPool.waitingCount,
    lastCheck: new Date()
  };
  
  // 如果连接池接近耗尽，发出警告
  if (connectionPoolStats.waitingClients > 5) {
    console.warn(`⚠️ 连接池负载较高: 等待客户端=${connectionPoolStats.waitingClients}, 总连接数=${connectionPoolStats.totalConnections}`);
  }
  
  if (connectionPoolStats.waitingClients > 10) {
    console.error(`🚨 连接池接近耗尽: 等待客户端=${connectionPoolStats.waitingClients}, 建议优化查询或增加连接数`);
  }
}, 30000); // 每30秒检查一次

// 优雅关闭函数
async function gracefulShutdown() {
  console.log('🔄 正在关闭数据库连接...');
  
  try {
    // 关闭数据库连接池
    await dbPool.end();
    console.log('✅ 数据库连接池已关闭');
  } catch (error) {
    console.error('❌ 关闭数据库连接池时出错:', error.message);
  }
  
  try {
    // 关闭Redis连接
    if (redisClient.isOpen) {
      await redisClient.quit();
      console.log('✅ Redis连接已关闭');
    }
  } catch (error) {
    console.error('❌ 关闭Redis连接时出错:', error.message);
  }
  
  console.log('👋 数据库资源清理完成');
}

// 连接测试函数
async function testDatabaseConnection(maxRetries = 3, retryDelay = 1000) {
  // 详细的环境变量解析日志
  console.log('🔍 数据库连接配置检查:');
  console.log(`  DB_HOST: "${process.env.DB_HOST || 'localhost'}"`);
  
  const dbPortStr = process.env.DB_PORT;
  const dbPortParsed = parseInt(dbPortStr);
  console.log(`  DB_PORT: 原始值="${dbPortStr}", 解析后=${dbPortParsed}, 使用端口=${dbPortParsed || 5432}`);
  
  console.log(`  DB_NAME: "${process.env.DB_NAME || 'bike_wholesale'}"`);
  console.log(`  DB_USER: "${process.env.DB_USER || 'postgres'}"`);
  console.log(`  DB_PASSWORD: "${process.env.DB_PASSWORD ? '***' : '未设置'}"`);
  console.log(`  DB_SSL: "${process.env.DB_SSL || 'false'}"`);
  
  // 检查端口解析是否正确
  if (dbPortStr && isNaN(dbPortParsed)) {
    console.warn(`⚠️ DB_PORT 解析警告: "${dbPortStr}" 不是有效的数字，将使用默认端口 5432`);
  }
  
  console.log('');
  
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      console.log(`🔗 尝试连接数据库 (${attempt}/${maxRetries})...`);
      
      // 使用超时包装数据库连接
      const connectionPromise = dbPool.connect();
      const timeoutPromise = new Promise((_, reject) => 
        setTimeout(() => reject(new Error('数据库连接超时')), 10000)
      );
      
      const client = await Promise.race([connectionPromise, timeoutPromise]);
      const result = await client.query('SELECT 1');
      client.release();
      console.log('✅ 数据库连接测试成功');
      isDatabaseAvailable = true; // 重置可用标志
      return true;
    } catch (error) {
      if (attempt < maxRetries) {
        console.warn(`⚠️ 数据库连接测试失败 (尝试 ${attempt}/${maxRetries}):`, error.message);
        console.log(`⏳ ${retryDelay/1000}秒后重试...`);
        await new Promise(resolve => setTimeout(resolve, retryDelay));
      } else {
        console.warn('⚠️ 数据库连接测试最终失败:', error.message);
        isDatabaseAvailable = false; // 设置不可用标志
        console.log('💡 服务器将在无数据库模式下运行');
        return false;
      }
    }
  }
}

async function testRedisConnection(maxRetries = 3, retryDelay = 1000) {
  // 详细的Redis环境变量解析日志
  console.log('🔍 Redis连接配置检查:');
  
  const redisUrl = process.env.REDIS_URL || `redis://${process.env.REDIS_HOST || 'localhost'}:${process.env.REDIS_PORT || 6379}`;
  console.log(`  REDIS_URL: "${redisUrl}"`);
  
  const redisPortStr = process.env.REDIS_PORT;
  const redisPortParsed = parseInt(redisPortStr);
  console.log(`  REDIS_PORT: 原始值="${redisPortStr}", 解析后=${redisPortParsed}, 使用端口=${redisPortParsed || 6379}`);
  
  console.log(`  REDIS_HOST: "${process.env.REDIS_HOST || 'localhost'}"`);
  console.log(`  REDIS_PASSWORD: "${process.env.REDIS_PASSWORD ? '***' : '未设置'}"`);
  
  const redisDbStr = process.env.REDIS_DB;
  const redisDbParsed = parseInt(redisDbStr);
  console.log(`  REDIS_DB: 原始值="${redisDbStr}", 解析后=${redisDbParsed}, 使用数据库=${redisDbParsed || 0}`);
  
  // 检查端口解析是否正确
  if (redisPortStr && isNaN(redisPortParsed)) {
    console.warn(`⚠️ REDIS_PORT 解析警告: "${redisPortStr}" 不是有效的数字，将使用默认端口 6379`);
  }
  
  // 检查数据库编号解析是否正确
  if (redisDbStr && isNaN(redisDbParsed)) {
    console.warn(`⚠️ REDIS_DB 解析警告: "${redisDbStr}" 不是有效的数字，将使用默认数据库 0`);
  }
  
  console.log('');
  
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      console.log(`🔗 尝试连接Redis (${attempt}/${maxRetries})...`);
      if (!redisClient.isOpen) {
        await redisClient.connect();
      }
      const pong = await redisClient.ping();
      console.log('✅ Redis连接测试成功:', pong);
      isRedisAvailable = true; // 重置可用标志
      return true;
    } catch (error) {
      if (attempt < maxRetries) {
        console.warn(`⚠️ Redis连接测试失败 (尝试 ${attempt}/${maxRetries}):`, error.message);
        console.log(`⏳ ${retryDelay/1000}秒后重试...`);
        await new Promise(resolve => setTimeout(resolve, retryDelay));
      } else {
        console.warn('⚠️ Redis连接测试最终失败:', error.message);
        isRedisAvailable = false; // 设置不可用标志
        console.log('💡 服务器将在无缓存模式下运行');
        return false;
      }
    }
  }
}

// 安全查询函数 - 在数据库不可用时返回降级结果
async function safeQuery(query, params = []) {
  if (!isDatabaseAvailable) {
    console.warn('⚠️ 数据库不可用，查询被跳过:', query.substring(0, 100) + '...');
    return { rows: [], rowCount: 0 };
  }
  
  try {
    return await dbPool.query(query, params);
  } catch (error) {
    console.error('❌ 数据库查询失败:', error.message);
    console.error('🔧 查询语句:', query.substring(0, 200));
    throw error; // 重新抛出错误，让调用方处理
  }
}

// 获取服务状态
function getServiceStatus() {
  return {
    database: {
      available: isDatabaseAvailable,
      errorCount: dbConnectionErrorCount,
      poolStats: connectionPoolStats
    },
    redis: {
      available: isRedisAvailable,
      connected: redisClient.isOpen
    },
    timestamp: new Date().toISOString()
  };
}

// 数据库初始化函数
async function initializeDatabase() {
  try {
    // 测试数据库连接
    await dbPool.query('SELECT 1');
    console.log('✅ 数据库连接测试成功');
    
    // 创建必要的表结构
    const createTablesQuery = `
      -- 用户表
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        role VARCHAR(20) DEFAULT 'user',
        avatar TEXT,
        last_login TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- 产品表
      CREATE TABLE IF NOT EXISTS products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        price DECIMAL(10,2) NOT NULL,
        category VARCHAR(50),
        image_url TEXT,
        stock_quantity INTEGER DEFAULT 0,
        status VARCHAR(20) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );



      -- 文章表
      CREATE TABLE IF NOT EXISTS articles (
        id SERIAL PRIMARY KEY,
        title VARCHAR(200) NOT NULL,
        content TEXT NOT NULL,
        category INTEGER,
        theme VARCHAR(100),
        keywords TEXT,
        status VARCHAR(20) DEFAULT 'draft',
        author_id INTEGER REFERENCES users(id),
        published_at TIMESTAMP,
        checksum VARCHAR(64),
        metadata TEXT, -- 改为TEXT类型，避免JSONB兼容性问题
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- 博客分类表
      CREATE TABLE IF NOT EXISTS blog_categories (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        slug VARCHAR(100) UNIQUE NOT NULL,
        description TEXT,
        parent_id INTEGER REFERENCES blog_categories(id),
        sort_order INTEGER DEFAULT 0,
        status VARCHAR(20) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        deleted_at TIMESTAMP
      );

      -- 博客栏目表
      CREATE TABLE IF NOT EXISTS blog_columns (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        slug VARCHAR(100) UNIQUE NOT NULL,
        description TEXT,
        sort_order INTEGER DEFAULT 0,
        status VARCHAR(20) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        deleted_at TIMESTAMP
      );

      -- 栏目分类关系表
      CREATE TABLE IF NOT EXISTS column_categories (
        column_id INTEGER REFERENCES blog_columns(id),
        category_id INTEGER REFERENCES blog_categories(id),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (column_id, category_id)
      );

      -- 评论表
      CREATE TABLE IF NOT EXISTS comments (
        id SERIAL PRIMARY KEY,
        article_id INTEGER REFERENCES articles(id) NOT NULL,
        parent_id INTEGER REFERENCES comments(id),
        author_name VARCHAR(100) NOT NULL,
        author_email VARCHAR(100) NOT NULL,
        content TEXT NOT NULL,
        status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'featured')),
        is_featured BOOLEAN DEFAULT FALSE,
        ip_address INET,
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- 通知表
      CREATE TABLE IF NOT EXISTS notifications (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        title VARCHAR(200) NOT NULL,
        message TEXT NOT NULL,
        type VARCHAR(50) DEFAULT 'system',
        read BOOLEAN DEFAULT FALSE,
        read_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- 日志表
      CREATE TABLE IF NOT EXISTS logs (
        id SERIAL PRIMARY KEY,
        level VARCHAR(20) NOT NULL,
        type VARCHAR(50) NOT NULL,
        message TEXT NOT NULL,
        details TEXT, -- 改为TEXT类型，避免JSONB兼容性问题
        user_id INTEGER,
        ip INET,
        user_agent TEXT,
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- 图库表
      CREATE TABLE IF NOT EXISTS gallery (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        image_url TEXT NOT NULL,
        thumbnail_url TEXT,
        file_size INTEGER,
        file_type VARCHAR(50),
        dimensions VARCHAR(50),
        category VARCHAR(100),
        tags TEXT[],
        usage_type VARCHAR(50) DEFAULT 'general', -- carousel, featured, about, blog, product
        is_active BOOLEAN DEFAULT true,
        sort_order INTEGER DEFAULT 0,
        created_by INTEGER REFERENCES users(id),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );

      -- 创建索引
      CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
      CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
      CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
      CREATE INDEX IF NOT EXISTS idx_products_status ON products(status);
      CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
      CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
      CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category);
      CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
      CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id);
      CREATE INDEX IF NOT EXISTS idx_notifications_read ON notifications(read);
      CREATE INDEX IF NOT EXISTS idx_logs_level ON logs(level);
      CREATE INDEX IF NOT EXISTS idx_logs_type ON logs(type);
      CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON logs(timestamp);
      CREATE INDEX IF NOT EXISTS idx_logs_user_id ON logs(user_id);
      
      -- 评论表索引
      CREATE INDEX IF NOT EXISTS idx_comments_article_id ON comments(article_id);
      CREATE INDEX IF NOT EXISTS idx_comments_parent_id ON comments(parent_id);
      CREATE INDEX IF NOT EXISTS idx_comments_status ON comments(status);
      CREATE INDEX IF NOT EXISTS idx_comments_is_featured ON comments(is_featured);
      CREATE INDEX IF NOT EXISTS idx_comments_created_at ON comments(created_at);
      
      -- 图库表索引
      CREATE INDEX IF NOT EXISTS idx_gallery_category ON gallery(category);
      CREATE INDEX IF NOT EXISTS idx_gallery_usage_type ON gallery(usage_type);
      CREATE INDEX IF NOT EXISTS idx_gallery_is_active ON gallery(is_active);
      CREATE INDEX IF NOT EXISTS idx_gallery_sort_order ON gallery(sort_order);
      CREATE INDEX IF NOT EXISTS idx_gallery_created_at ON gallery(created_at);
    `;

    await dbPool.query(createTablesQuery);
    console.log('✅ 数据库表结构初始化完成');

    // 创建默认管理员用户（如果不存在）
    const adminCheck = await dbPool.query('SELECT id FROM users WHERE email = $1', ['admin@example.com']);
    if (adminCheck.rows.length === 0) {
      try {
        const bcrypt = await import('bcryptjs');
        const hashedPassword = await bcrypt.hash('admin123', 12);
        
        await dbPool.query(`
          INSERT INTO users (username, email, password_hash, role) 
          VALUES ($1, $2, $3, $4)
        `, ['admin', 'admin@example.com', hashedPassword, 'admin']);
        
        console.log('✅ 默认管理员用户创建完成');
      } catch (error) {
        console.warn('⚠️ 创建默认管理员用户失败:', error.message);
        console.warn('💡 这不会影响服务器启动，但需要手动创建管理员用户');
      }
    }

  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
    // 不再抛出错误，而是记录警告并继续运行
    console.warn('⚠️ 数据库初始化失败，但服务器将继续以降级模式运行');
    console.warn('💡 部分功能可能不可用，但API服务仍可访问');
  }
}

export { 
  dbPool, 
  redisClient, 
  initializeDatabase,
  testDatabaseConnection,
  testRedisConnection,
  gracefulShutdown,
  safeQuery,
  getServiceStatus,
  isDatabaseAvailable,
  isRedisAvailable
};